package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.datacenter.dto.AdRecoveryDto;
import com.dy.yunying.api.datacenter.vo.AdRecoveryVo;
import com.dy.yunying.biz.config.YunYingProperties;
import com.dy.yunying.biz.utils.DateUtils;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;

/**
 * @author ：lile
 * @date ：2021/6/22 11:04
 * @description：
 * @modified By：
 */
@Component(value = "dcAdRecoveryDao")
@Slf4j
public class AdRecoveryDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	private final static int[] NUMS_ = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 45, 60, 75, 90, 120, 150, 180};

	@Resource
	private YunYingProperties yunYingProperties;

	public List<AdRecoveryVo> selectAdRecoverySource(AdRecoveryDto req) {
		String indent = StringUtils.EMPTY;
		StringBuilder sql = new StringBuilder();
		this.getSql(sql, req, indent);
		if (StringUtils.isNotBlank(req.getKpiValue()) && StringUtils.isNotBlank(req.getSort())) {
			sql.append("ORDER BY\n");
			sql.append("    ").append(req.getKpiValue()).append(" ").append(req.getSort()).append('\n');
		} else {
			sql.append("ORDER BY\n");
			sql.append("    period DESC\n");
		}
		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append("LIMIT\n");
			sql.append("    ").append(offset).append(", ").append(size).append('\n');
		}

		log.info("回收分析.sql:[{}]", sql.toString());

		List<AdRecoveryVo> data = new ArrayList<>();
		long start = System.currentTimeMillis();
		SqlRowSet rs = clickhouseTemplate.queryForRowSet(sql.toString());
		long end = System.currentTimeMillis();
		log.info("sql : [{}]", "回收分析报表，耗时：" + (end - start) + "毫秒");
		List<String> columns = Arrays.asList(rs.getMetaData().getColumnNames());

		//遍历 ResultSet
		while (rs.next()) {
			//组装成对象 添加到结果集
			AdRecoveryVo item = ObjByRow(rs, columns, "period", req);
			data.add(item);
		}
		return data;
	}


	public Long countDataTotal(AdRecoveryDto req) {
		final String indent = "        ";
		final StringBuilder sql = new StringBuilder();
		sql.append("SELECT\n");
		sql.append("    COUNT(1)\n");
		sql.append("FROM\n");
		sql.append("    (\n");
		this.getSql(sql, req, indent);
		sql.append("    )\n");
		return clickhouseTemplate.queryForObject(sql.toString(), Long.class);
	}

	@SneakyThrows
	private void setRoi(Integer num, BigDecimal cost, AdRecoveryVo vo, SqlRowSet rs) {
		Field setR = vo.getClass().getDeclaredField("roi" + num);
		setR.setAccessible(true);
		Field setRN = vo.getClass().getDeclaredField(String.format("roi%sNum", num));
		setRN.setAccessible(true);
		//绑定数据到 roi1
		String worthKey = "worth" + num;
		BigDecimal worth = ObjectUtils.defaultIfNull(rs.getBigDecimal(worthKey), BigDecimal.ZERO);
		if (cost.equals(BigDecimal.ZERO)) {
			setR.set(vo, BigDecimal.ZERO);
		} else {
			setR.set(vo, worth.multiply(new BigDecimal(100)).divide(cost, NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
		}
		setRN.set(vo, worth.setScale(NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
	}

	//1 按天 2 按周  3  按月 4 汇总（默认）
	@SneakyThrows
	private void setLtv(Integer num, Integer divideNum, AdRecoveryVo vo, SqlRowSet rs,AdRecoveryDto req) {
		Field setR = vo.getClass().getDeclaredField("ltv" + num);
		setR.setAccessible(true);
		Field setRN = vo.getClass().getDeclaredField(String.format("ltv%sNum", num));
		setRN.setAccessible(true);
		String key = String.format("worth%s", num);
		BigDecimal worth = ObjectUtils.defaultIfNull(rs.getBigDecimal(key), BigDecimal.ZERO);
		Long rsTime = req.getRsTime();
		Date rsTimeDate = DateUtils.stringToDate(rsTime.toString(),DateUtils.YYYYMMDD);
		String period = vo.getPeriod();
		switch (req.getCycleType()){
			case 1:
				Date periodDate = DateUtils.stringToDate(period,DateUtils.YYYYMMDD);
				int n = DateUtils.betweenDays(periodDate,new Date());
				if(num > n + 1){
					setR.set(vo, BigDecimal.ZERO);
					setRN.set(vo, BigDecimal.ZERO);
				}else {
					if (divideNum == 0) {
						setR.set(vo, BigDecimal.ZERO);
					} else {
						setR.set(vo, worth.divide(new BigDecimal(divideNum), NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
					}
					setRN.set(vo, worth.setScale(NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
				}
				break;
			case 2:
				period = period.substring(0,period.indexOf(" "));
				Date zperiod = DateUtils.stringToDate(period,DateUtils.YYYY_MM_DD);
				zperiod = zperiod.compareTo(rsTimeDate)>0? zperiod:rsTimeDate;
				int z = DateUtils.betweenDays(zperiod,new Date());
				if(num > z + 1){
					setR.set(vo, BigDecimal.ZERO);
					setRN.set(vo, BigDecimal.ZERO);
				}else {
					if (divideNum == 0) {
						setR.set(vo, BigDecimal.ZERO);
					} else {
						setR.set(vo, worth.divide(new BigDecimal(divideNum), NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
					}
					setRN.set(vo, worth.setScale(NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
				}
				break;
			case 3:
				Date mperiod = DateUtils.stringToDate(period,DateUtils.YYYY_MM);
				int m = DateUtils.betweenDays(mperiod,new Date());
				if(num > m + 1){
					setR.set(vo, BigDecimal.ZERO);
					setRN.set(vo, BigDecimal.ZERO);
				}else {
					if (divideNum == 0) {
						setR.set(vo, BigDecimal.ZERO);
					} else {
						setR.set(vo, worth.divide(new BigDecimal(divideNum), NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
					}
					setRN.set(vo, worth.setScale(NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
				}
				break;
			default:
				int hz = DateUtils.betweenDays(rsTimeDate,new Date());
				if(num > hz + 1){
					setR.set(vo, BigDecimal.ZERO);
					setRN.set(vo, BigDecimal.ZERO);
				}else {
					if (divideNum == 0) {
						setR.set(vo, BigDecimal.ZERO);
					} else {
						setR.set(vo, worth.divide(new BigDecimal(divideNum), NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
					}
					setRN.set(vo, worth.setScale(NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
				}
				break;
		}


	}

	@SneakyThrows
	private void setLtvTimes(Integer num, BigDecimal worth1, AdRecoveryVo vo, SqlRowSet rs, AdRecoveryDto req) {
		Field setR = vo.getClass().getDeclaredField("ltvtimes" + num);
		setR.setAccessible(true);
		Field setRN = vo.getClass().getDeclaredField(String.format("ltvtimes%sNum", num));
		setRN.setAccessible(true);

		Long rsTime = req.getRsTime();
		Date rsTimeDate = DateUtils.stringToDate(rsTime.toString(),DateUtils.YYYYMMDD);
		String period = vo.getPeriod();
		switch (req.getCycleType()){
			case 1:
				Date periodDate = DateUtils.stringToDate(period,DateUtils.YYYYMMDD);
				int n = DateUtils.betweenDays(periodDate,new Date());
				if(num > n + 1){
					setR.set(vo, BigDecimal.ZERO);
					setRN.set(vo, BigDecimal.ZERO);
				}else {
					if (null != worth1 && worth1.compareTo(BigDecimal.ZERO) > 0) {
						String key = "worth" + num;
						BigDecimal worth = ObjectUtils.defaultIfNull(rs.getBigDecimal(key), BigDecimal.ZERO);
						setR.set(vo, worth.divide(worth1, NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
						setRN.set(vo, worth.setScale(NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
					} else {
						setR.set(vo, BigDecimal.ZERO);
						setRN.set(vo, BigDecimal.ZERO);
					}
				}
				break;
			case 2:
				period = period.substring(0,period.indexOf(" "));
				Date zperiod = DateUtils.stringToDate(period,DateUtils.YYYY_MM_DD);
				zperiod = zperiod.compareTo(rsTimeDate)>0? zperiod:rsTimeDate;
				int z = DateUtils.betweenDays(zperiod,new Date());
				if(num > z + 1){
					setR.set(vo, BigDecimal.ZERO);
					setRN.set(vo, BigDecimal.ZERO);
				}else {
					if (null != worth1 && worth1.compareTo(BigDecimal.ZERO) > 0) {
						String key = "worth" + num;
						BigDecimal worth = ObjectUtils.defaultIfNull(rs.getBigDecimal(key), BigDecimal.ZERO);
						setR.set(vo, worth.divide(worth1, NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
						setRN.set(vo, worth.setScale(NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
					} else {
						setR.set(vo, BigDecimal.ZERO);
						setRN.set(vo, BigDecimal.ZERO);
					}
				}
				break;
			case 3:
				Date mperiod = DateUtils.stringToDate(period,DateUtils.YYYY_MM);
				int m = DateUtils.betweenDays(mperiod,new Date());
				if(num > m + 1){
					setR.set(vo, BigDecimal.ZERO);
					setRN.set(vo, BigDecimal.ZERO);
				}else {
					if (null != worth1 && worth1.compareTo(BigDecimal.ZERO) > 0) {
						String key = "worth" + num;
						BigDecimal worth = ObjectUtils.defaultIfNull(rs.getBigDecimal(key), BigDecimal.ZERO);
						setR.set(vo, worth.divide(worth1, NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
						setRN.set(vo, worth.setScale(NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
					} else {
						setR.set(vo, BigDecimal.ZERO);
						setRN.set(vo, BigDecimal.ZERO);
					}
				}
				break;
			default:
				int hz = DateUtils.betweenDays(rsTimeDate,new Date());
				if(num > hz + 1){
					setR.set(vo, BigDecimal.ZERO);
					setRN.set(vo, BigDecimal.ZERO);
				}else {
					if (null != worth1 && worth1.compareTo(BigDecimal.ZERO) > 0) {
						String key = "worth" + num;
						BigDecimal worth = ObjectUtils.defaultIfNull(rs.getBigDecimal(key), BigDecimal.ZERO);
						setR.set(vo, worth.divide(worth1, NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
						setRN.set(vo, worth.setScale(NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
					} else {
						setR.set(vo, BigDecimal.ZERO);
						setRN.set(vo, BigDecimal.ZERO);
					}
				}
				break;
		}
	}

	private AdRecoveryVo ObjByRow(SqlRowSet rs, List<String> columns, String type, AdRecoveryDto req) {
		AdRecoveryVo vo = new AdRecoveryVo();
		vo.setPeriod(rs.getString("period"));
		vo.setCost(rs.getBigDecimal("cost"));
		vo.setRudeCost(rs.getBigDecimal("rudeCost"));

		//投放人id
		if (columns.contains("investor") && StringUtils.isNotBlank(rs.getString("investor"))) {
			vo.setInvestor(rs.getString("investor"));
		} else {
			vo.setInvestor("-");
		}
		//投放人名称
		if (columns.contains("investorName") && StringUtils.isNotBlank(rs.getString("investorName"))) {
			vo.setInvestorName(rs.getString("investorName"));
		} else {
			vo.setInvestorName("-");
		}

		//部门维度 部门名称
		if (columns.contains("deptName") && StringUtils.isNotBlank(rs.getString("deptName"))) {
			vo.setDeptName(rs.getString("deptName"));
		} else {
			vo.setDeptName("-");
		}

		//部门维度 部门id
		if (columns.contains("deptId") && StringUtils.isNotBlank(rs.getString("deptId"))) {
			vo.setDeptId(rs.getString("deptId"));
		} else {
			vo.setDeptId("-");
		}

		//部门维度 部门id
		if (columns.contains("gameid") && StringUtils.isNotBlank(rs.getString("gameid"))) {
			vo.setGameid(rs.getLong("gameid"));
		}

		//组别维度 部门名称
		if (columns.contains("userGroupName") && StringUtils.isNotBlank(rs.getString("userGroupName"))) {
			vo.setUserGroupName(rs.getString("userGroupName"));
		} else {
			vo.setUserGroupName("-");
		}

		//组别维度 部门id
		if (columns.contains("userGroupId") && StringUtils.isNotBlank(rs.getString("userGroupId"))) {
			vo.setUserGroupId(rs.getString("userGroupId"));
		} else {
			vo.setUserGroupId("-");
		}
		//主渠道
		if (columns.contains("parentchl") && StringUtils.isNotBlank(rs.getString("parentchl"))) {
			vo.setParentchl(rs.getString("parentchl"));
		} else {
			vo.setParentchl("-");
		}

		//父游戏 id
		if (columns.contains("pgid")) {
			vo.setPgid(rs.getLong("pgid"));
		}

		//系统
		if (columns.contains("os")) {
			vo.setOs(rs.getInt("os"));
		}

		// 广告计划
		if (columns.contains("adid")) {
			vo.setAdid(rs.getString("adid"));
		}

		//分包
		if (columns.contains("appchl") && StringUtils.isNotBlank(rs.getString("appchl"))) {
			vo.setAppchl(rs.getString("appchl"));
		} else {
			vo.setAppchl("-");
		}

		// 新增设备充值数
		Integer divideNum = rs.getInt("usrnamenums");
		BigDecimal cost = rs.getBigDecimal("cost");
		vo.setCost(cost);

		if (columns.contains("usrnamenums")) {
			vo.setUsrnamenums(rs.getInt("usrnamenums"));
		}

		//新增设备成本
		vo.setDeviceCose(rs.getBigDecimal("deviceCose"));

		//累计ROI
		vo.setRoiRatio(rs.getBigDecimal("roiRatio"));
		if (cost.equals(BigDecimal.ZERO)) {
			vo.setRoiRatioNum(BigDecimal.ZERO);
		} else {
			BigDecimal worth = ObjectUtils.defaultIfNull(rs.getBigDecimal("worth"), BigDecimal.ZERO);
			vo.setRoiRatioNum(worth.setScale(NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
		}
		for (int i : NUMS_) {
			setRoi(i, cost, vo, rs);
		}

		// 累计LTV
		if (divideNum == 0) {
			vo.setLtvRatio(BigDecimal.ZERO);
		} else {
			BigDecimal worth = ObjectUtils.defaultIfNull(rs.getBigDecimal("worth"), BigDecimal.ZERO);
			vo.setLtvRatio(worth.divide(new BigDecimal(divideNum), NumberUtils.INTEGER_TWO, RoundingMode.HALF_UP));
		}
		for (int i : NUMS_) {
			setLtv(i, divideNum, vo, rs, req);
		}

		BigDecimal worth1 = ObjectUtils.defaultIfNull(rs.getBigDecimal("worth1"), BigDecimal.ZERO);
		for (int i : NUMS_) {
			setLtvTimes(i, worth1, vo, rs, req);
		}

		return vo;
	}

	// 每个指标汇总sql
	private void getSql(StringBuilder querySql, AdRecoveryDto req, String indent) {
		final String subIndent = indent + "        ";
		querySql.append(indent).append("SELECT\n");
		querySql.append(indent).append("    ").append(this.getPeriodSql(req)).append(" AS period, ").append(this.getQueryColumnSql(req)).append('\n');
		querySql.append(indent).append("    toDecimal64(round(rudeCost, 2), 2) rudeCost, -- 原始消耗\n");
		querySql.append(indent).append("    toDecimal64(round(cost, 2), 2) cost, -- 返点后消耗\n");
		querySql.append(indent).append("    usrnamenums, -- 新增设备注册数\n");
		querySql.append(indent).append("    toDecimal64(ifNull(round(cost / IF(usrnamenums > 0, usrnamenums, NULL), 2), 0), 2) deviceCose, -- 设备成本\n");
		querySql.append(indent).append("    toDecimal64(ifNull(round(worth * 100 / IF(cost > 0, cost, NULL), 2), 0), 2) roiRatio, -- 累计Roi累计充值金额/返点后消耗\n");
		querySql.append(indent).append("    toDecimal64(round(worth, 2), 2) AS worth,\n");
		querySql.append(indent).append("    toDecimal64(round(worth1, 2), 2) AS worth1,\n");
		querySql.append(indent).append("    toDecimal64(round(worth2, 2), 2) AS worth2,\n");
		querySql.append(indent).append("    toDecimal64(round(worth3, 2), 2) AS worth3,\n");
		querySql.append(indent).append("    toDecimal64(round(worth4, 2), 2) AS worth4,\n");
		querySql.append(indent).append("    toDecimal64(round(worth5, 2), 2) AS worth5,\n");
		querySql.append(indent).append("    toDecimal64(round(worth6, 2), 2) AS worth6,\n");
		querySql.append(indent).append("    toDecimal64(round(worth7, 2), 2) AS worth7,\n");
		querySql.append(indent).append("    toDecimal64(round(worth8, 2), 2) AS worth8,\n");
		querySql.append(indent).append("    toDecimal64(round(worth9, 2), 2) AS worth9,\n");
		querySql.append(indent).append("    toDecimal64(round(worth10, 2), 2) AS worth10,\n");
		querySql.append(indent).append("    toDecimal64(round(worth11, 2), 2) AS worth11,\n");
		querySql.append(indent).append("    toDecimal64(round(worth12, 2), 2) AS worth12,\n");
		querySql.append(indent).append("    toDecimal64(round(worth13, 2), 2) AS worth13,\n");
		querySql.append(indent).append("    toDecimal64(round(worth14, 2), 2) AS worth14,\n");
		querySql.append(indent).append("    toDecimal64(round(worth15, 2), 2) AS worth15,\n");
		querySql.append(indent).append("    toDecimal64(round(worth16, 2), 2) AS worth16,\n");
		querySql.append(indent).append("    toDecimal64(round(worth17, 2), 2) AS worth17,\n");
		querySql.append(indent).append("    toDecimal64(round(worth18, 2), 2) AS worth18,\n");
		querySql.append(indent).append("    toDecimal64(round(worth19, 2), 2) AS worth19,\n");
		querySql.append(indent).append("    toDecimal64(round(worth20, 2), 2) AS worth20,\n");
		querySql.append(indent).append("    toDecimal64(round(worth21, 2), 2) AS worth21,\n");
		querySql.append(indent).append("    toDecimal64(round(worth22, 2), 2) AS worth22,\n");
		querySql.append(indent).append("    toDecimal64(round(worth23, 2), 2) AS worth23,\n");
		querySql.append(indent).append("    toDecimal64(round(worth24, 2), 2) AS worth24,\n");
		querySql.append(indent).append("    toDecimal64(round(worth25, 2), 2) AS worth25,\n");
		querySql.append(indent).append("    toDecimal64(round(worth26, 2), 2) AS worth26,\n");
		querySql.append(indent).append("    toDecimal64(round(worth27, 2), 2) AS worth27,\n");
		querySql.append(indent).append("    toDecimal64(round(worth28, 2), 2) AS worth28,\n");
		querySql.append(indent).append("    toDecimal64(round(worth29, 2), 2) AS worth29,\n");
		querySql.append(indent).append("    toDecimal64(round(worth30, 2), 2) AS worth30,\n");
		querySql.append(indent).append("    toDecimal64(round(worth45, 2), 2) AS worth45,\n");
		querySql.append(indent).append("    toDecimal64(round(worth60, 2), 2) AS worth60,\n");
		querySql.append(indent).append("    toDecimal64(round(worth75, 2), 2) AS worth75,\n");
		querySql.append(indent).append("    toDecimal64(round(worth90, 2), 2) AS worth90,\n");
		querySql.append(indent).append("    toDecimal64(round(worth120, 2), 2) AS worth120,\n");
		querySql.append(indent).append("    toDecimal64(round(worth150, 2), 2) AS worth150,\n");
		querySql.append(indent).append("    toDecimal64(round(worth180, 2), 2) AS worth180\n");
		querySql.append(indent).append("FROM\n");
		querySql.append(indent).append("    (\n");
		this.getPayNSql(querySql, req, subIndent);
		querySql.append(indent).append("    ) a \n");
		querySql.append(indent).append("    FULL JOIN (\n");
		this.getCostSql(querySql, req, subIndent);
		querySql.append(indent).append("    ) b USING (").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req)).append(")\n");
	}

	//计算N日充值金额
	private void getPayNSql(StringBuilder deviceSql, AdRecoveryDto req, String indent) {
		final Integer showRatio = req.getShowRatio();
		final String sharingStr = null != showRatio && 0 == req.getShowRatio() ? StringUtils.EMPTY : " * sharing";
		deviceSql.append(indent).append("SELECT\n");
		deviceSql.append(indent).append("    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append('\n');
		deviceSql.append(indent).append("    SUM(IF(LENGTH(latest_username) > 0, 1, 0)) AS usrnamenums, -- 新增设备注册数\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_total").append(sharingStr).append("), 0) AS worth,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_1").append(sharingStr).append("), 0) AS worth1,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_2").append(sharingStr).append("), 0) AS worth2,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_3").append(sharingStr).append("), 0) AS worth3,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_4").append(sharingStr).append("), 0) AS worth4,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_5").append(sharingStr).append("), 0) AS worth5,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_6").append(sharingStr).append("), 0) AS worth6,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_7").append(sharingStr).append("), 0) AS worth7,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_8").append(sharingStr).append("), 0) AS worth8,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_9").append(sharingStr).append("), 0) AS worth9,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_10").append(sharingStr).append("), 0) AS worth10,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_11").append(sharingStr).append("), 0) AS worth11,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_12").append(sharingStr).append("), 0) AS worth12,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_13").append(sharingStr).append("), 0) AS worth13,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_14").append(sharingStr).append("), 0) AS worth14,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_15").append(sharingStr).append("), 0) AS worth15,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_16").append(sharingStr).append("), 0) AS worth16,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_17").append(sharingStr).append("), 0) AS worth17,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_18").append(sharingStr).append("), 0) AS worth18,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_19").append(sharingStr).append("), 0) AS worth19,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_20").append(sharingStr).append("), 0) AS worth20,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_21").append(sharingStr).append("), 0) AS worth21,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_22").append(sharingStr).append("), 0) AS worth22,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_23").append(sharingStr).append("), 0) AS worth23,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_24").append(sharingStr).append("), 0) AS worth24,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_25").append(sharingStr).append("), 0) AS worth25,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_26").append(sharingStr).append("), 0) AS worth26,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_27").append(sharingStr).append("), 0) AS worth27,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_28").append(sharingStr).append("), 0) AS worth28,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_29").append(sharingStr).append("), 0) AS worth29,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_30").append(sharingStr).append("), 0) AS worth30,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_45").append(sharingStr).append("), 0) AS worth45,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_60").append(sharingStr).append("), 0) AS worth60,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_75").append(sharingStr).append("), 0) AS worth75,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_90").append(sharingStr).append("), 0) AS worth90,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_120").append(sharingStr).append("), 0) AS worth120,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_150").append(sharingStr).append("), 0) AS worth150,\n");
		deviceSql.append(indent).append("    COALESCE(SUM(fee_180").append(sharingStr).append("), 0) AS worth180\n");
		deviceSql.append(indent).append("FROM\n");
		deviceSql.append(indent).append("    (\n");
		deviceSql.append(indent).append("        SELECT\n");
		deviceSql.append(indent).append("            reg_day AS day, week, month, year, kid, collect, game_main AS pgid, os, game_sub AS gameid, chl_main AS parentchl, chl_sub AS chl, chl_base AS appchl, ad_id AS adid, ad_account AS advertiserid, latest_username, first_pay_day,\n");
		deviceSql.append(indent).append("            dateDiff('day', parseDateTimeBestEffort(toString(reg_day)), today()) AS diff_days, fee_total, fee_1,\n");
		//ROI 增长
		if(req.getRetentionKpi() == 3) {
			deviceSql.append(indent).append("            IF(diff_days < 1, 0, fee_2) AS fee_2,\n");
			deviceSql.append(indent).append("            IF(diff_days < 2, 0, fee_3) AS fee_3,\n");
			deviceSql.append(indent).append("            IF(diff_days < 3, 0, fee_4) AS fee_4,\n");
			deviceSql.append(indent).append("            IF(diff_days < 4, 0, fee_5) AS fee_5,\n");
			deviceSql.append(indent).append("            IF(diff_days < 5, 0, fee_6) AS fee_6,\n");
			deviceSql.append(indent).append("            IF(diff_days < 6, 0, fee_7) AS fee_7,\n");
			deviceSql.append(indent).append("            IF(diff_days < 7, 0, fee_8) AS fee_8,\n");
			deviceSql.append(indent).append("            IF(diff_days < 8, 0, fee_9) AS fee_9,\n");
			deviceSql.append(indent).append("            IF(diff_days < 9, 0, fee_10) AS fee_10,\n");
			deviceSql.append(indent).append("            IF(diff_days < 10, 0, fee_11) AS fee_11,\n");
			deviceSql.append(indent).append("            IF(diff_days < 11, 0, fee_12) AS fee_12,\n");
			deviceSql.append(indent).append("            IF(diff_days < 12, 0, fee_13) AS fee_13,\n");
			deviceSql.append(indent).append("            IF(diff_days < 13, 0, fee_14) AS fee_14,\n");
			deviceSql.append(indent).append("            IF(diff_days < 14, 0, fee_15) AS fee_15,\n");
			deviceSql.append(indent).append("            IF(diff_days < 15, 0, fee_16) AS fee_16,\n");
			deviceSql.append(indent).append("            IF(diff_days < 16, 0, fee_17) AS fee_17,\n");
			deviceSql.append(indent).append("            IF(diff_days < 17, 0, fee_18) AS fee_18,\n");
			deviceSql.append(indent).append("            IF(diff_days < 18, 0, fee_19) AS fee_19,\n");
			deviceSql.append(indent).append("            IF(diff_days < 19, 0, fee_20) AS fee_20,\n");
			deviceSql.append(indent).append("            IF(diff_days < 20, 0, fee_21) AS fee_21,\n");
			deviceSql.append(indent).append("            IF(diff_days < 21, 0, fee_22) AS fee_22,\n");
			deviceSql.append(indent).append("            IF(diff_days < 22, 0, fee_23) AS fee_23,\n");
			deviceSql.append(indent).append("            IF(diff_days < 23, 0, fee_24) AS fee_24,\n");
			deviceSql.append(indent).append("            IF(diff_days < 24, 0, fee_25) AS fee_25,\n");
			deviceSql.append(indent).append("            IF(diff_days < 25, 0, fee_26) AS fee_26,\n");
			deviceSql.append(indent).append("            IF(diff_days < 26, 0, fee_27) AS fee_27,\n");
			deviceSql.append(indent).append("            IF(diff_days < 27, 0, fee_28) AS fee_28,\n");
			deviceSql.append(indent).append("            IF(diff_days < 28, 0, fee_29) AS fee_29,\n");
			deviceSql.append(indent).append("            IF(diff_days < 29, 0, fee_30) AS fee_30,\n");
			deviceSql.append(indent).append("            IF(diff_days < 44, 0, fee_45) AS fee_45,\n");
			deviceSql.append(indent).append("            IF(diff_days < 59, 0, fee_60) AS fee_60,\n");
			deviceSql.append(indent).append("            IF(diff_days < 74, 0, fee_75) AS fee_75,\n");
			deviceSql.append(indent).append("            IF(diff_days < 89, 0, fee_90) AS fee_90,\n");
			deviceSql.append(indent).append("            IF(diff_days < 119, 0, fee_120) AS fee_120,\n");
			deviceSql.append(indent).append("            IF(diff_days < 149, 0, fee_150) AS fee_150,\n");
			deviceSql.append(indent).append("            IF(diff_days < 179, 0, fee_180) AS fee_180\n");
		}else {
			//LTV LTV增长倍数
			deviceSql.append(indent).append("            fee_2,\n");
			deviceSql.append(indent).append("            fee_3,\n");
			deviceSql.append(indent).append("            fee_4,\n");
			deviceSql.append(indent).append("            fee_5,\n");
			deviceSql.append(indent).append("            fee_6,\n");
			deviceSql.append(indent).append("            fee_7,\n");
			deviceSql.append(indent).append("            fee_8,\n");
			deviceSql.append(indent).append("            fee_9,\n");
			deviceSql.append(indent).append("            fee_10,\n");
			deviceSql.append(indent).append("            fee_11,\n");
			deviceSql.append(indent).append("            fee_12,\n");
			deviceSql.append(indent).append("            fee_13,\n");
			deviceSql.append(indent).append("            fee_14,\n");
			deviceSql.append(indent).append("            fee_15,\n");
			deviceSql.append(indent).append("            fee_16,\n");
			deviceSql.append(indent).append("            fee_17,\n");
			deviceSql.append(indent).append("            fee_18,\n");
			deviceSql.append(indent).append("            fee_19,\n");
			deviceSql.append(indent).append("            fee_20,\n");
			deviceSql.append(indent).append("            fee_21,\n");
			deviceSql.append(indent).append("            fee_22,\n");
			deviceSql.append(indent).append("            fee_23,\n");
			deviceSql.append(indent).append("            fee_24,\n");
			deviceSql.append(indent).append("            fee_25,\n");
			deviceSql.append(indent).append("            fee_26,\n");
			deviceSql.append(indent).append("            fee_27,\n");
			deviceSql.append(indent).append("            fee_28,\n");
			deviceSql.append(indent).append("            fee_29,\n");
			deviceSql.append(indent).append("            fee_30,\n");
			deviceSql.append(indent).append("            fee_45,\n");
			deviceSql.append(indent).append("            fee_60,\n");
			deviceSql.append(indent).append("            fee_75,\n");
			deviceSql.append(indent).append("            fee_90,\n");
			deviceSql.append(indent).append("            fee_120,\n");
			deviceSql.append(indent).append("            fee_150,\n");
			deviceSql.append(indent).append("            fee_180\n");
		}
		deviceSql.append(indent).append("        FROM\n");
		deviceSql.append(indent).append("            ").append(yunYingProperties.getNinetydeviceregtable()).append('\n');
		deviceSql.append(indent).append("        WHERE\n");
		deviceSql.append(indent).append("            spread_type = 1 AND reg_day >= ").append(req.getRsTime()).append(" AND reg_day <= ").append(req.getReTime()).append(this.selectCondition(req, " ")).append(this.selectAdCondition(req, " ")).append('\n');
		deviceSql.append(indent).append("    ) reg\n");
		deviceSql.append(indent).append("    LEFT JOIN (SELECT CAST(id AS Int16) AS id, (1 - COALESCE(sharing, 0)) AS sharing FROM dim_200_pangu_mysql_parent_game) pg ON reg.pgid = pg.id\n");
		deviceSql.append(indent).append("    LEFT JOIN (SELECT manage AS investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name, '-') userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = reg.parentchl AND wpc.chncode = reg.chl\n");
		deviceSql.append(indent).append("WHERE\n");
		deviceSql.append(indent).append("    1 = 1");
		if (req.getIsSys() != 1) {// -- 渠道权限
			deviceSql.append(" AND investor IN (").append(req.getUserIds()).append(')');
		}
		deviceSql.append(this.selectComConditionWd(req, "")).append('\n');
		deviceSql.append(indent).append("GROUP BY\n");
		deviceSql.append(indent).append("    ").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req)).append('\n');
	}

	//	消耗、返点后消耗
	private void getCostSql(StringBuilder costSql, AdRecoveryDto req, String indent) {
		costSql.append(indent).append("SELECT\n");
		costSql.append(indent).append("    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append('\n');
		costSql.append(indent).append("    COALESCE(SUM(rudeCost), 0) rudeCost, -- 原始消耗\n");
		costSql.append(indent).append("    COALESCE(SUM(cost), 0) cost -- 返点后消耗\n");
		costSql.append(indent).append("FROM\n");
		costSql.append(indent).append("    (\n");
		costSql.append(indent).append("        SELECT\n");
		costSql.append(indent).append("            day, week, month, year, collect, ad_show shownums, click clicknums, ad_id adid, ad_account advertiserid, COALESCE(rude_cost,0) rudeCost, COALESCE(cost,0) cost\n");
		costSql.append(indent).append("        FROM\n");
		costSql.append(indent).append("            ").append(yunYingProperties.getAdidrebatetable()).append('\n');
		costSql.append(indent).append("        WHERE\n");
		costSql.append(indent).append("            day >= ").append(req.getRsTime()).append(" AND day <= ").append(req.getReTime());
		if (req.getIsSys() != 1) { // -- 广告权限
			// 广告账户权限权限AdAccounts返回空
			if (!"'NO'".equals(req.getAdAccounts())) {
				costSql.append(" AND ad_account IN (").append(req.getAdAccounts()).append(')');
			}
		}
		costSql.append(this.selectAdCondition(req, "")).append('\n');
		costSql.append(indent).append("    ) ard\n");
		costSql.append(indent).append("    LEFT JOIN (SELECT os, game_main pgid, game_sub gameid, chl_main parentchl, chl_sub chl, chl_base appchl, ad_id FROM ").append(yunYingProperties.getAdptypetable()).append(") ap ON ard.adid = ap.ad_id\n");
		costSql.append(indent).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name,'-') userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = ap.parentchl AND wpc.chncode = ap.chl\n");
		costSql.append(indent).append("WHERE\n");
		costSql.append(indent).append("    1 = 1").append(this.selectComConditionWd(req, "")).append(this.selectCommonCondition(req, "")).append('\n');
		costSql.append(indent).append("GROUP BY\n");
		costSql.append(indent).append("    ").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req)).append('\n');
	}


	//周期 周月
	private StringBuilder getPeriodSql(AdRecoveryDto req) {
		String period = req.getPeriod();
		StringBuilder sql = new StringBuilder();
		sql.append(period);
		return sql;
	}

	//类别参数
	private StringBuilder getQueryColumnSql(AdRecoveryDto req) {
		StringBuilder sql = new StringBuilder();
		StringBuilder queryColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();

		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("deptId")) {
				queryColumnSql.append("deptName, ");
			}
			if (queryColumn.contains("investor")) {
				queryColumnSql.append("investorName, ");
			}
			if (queryColumn.contains("userGroupId")) {
				queryColumnSql.append("userGroupName, ");
			}
			queryColumnSql.append(queryColumn).append(", ");
		}
		sql.append(queryColumnSql);
		return sql;
	}


	//分组类别条件
	private StringBuilder getGroupColumnSql(AdRecoveryDto req) {
		String queryColumn = req.getQueryColumn();

		StringBuilder sql = new StringBuilder();
		StringBuilder groupColumnSql = new StringBuilder();

		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("deptId")) {
				groupColumnSql.append(", deptName");
			}
			if (queryColumn.contains("investor")) {
				groupColumnSql.append(", investorName");
			}
			if (queryColumn.contains("userGroupId")) {
				groupColumnSql.append(", userGroupName");

			}
			groupColumnSql.append(", ").append(queryColumn);
		}
		sql.append(groupColumnSql);
		return sql;
	}


	// 计划
	public String selectAdCondition(AdRecoveryDto req, String bieming) {
		StringBuilder commCondSB = new StringBuilder();
		if (StringUtils.isNotBlank(req.getAdidArr())) {
			commCondSB.append(" AND ad_id IN ('").append(req.getAdidArr().replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(req.getAdvertiserIdArr())) {
			commCondSB.append(" AND ad_account IN ('").append(req.getAdvertiserIdArr().replaceAll(",", "','")).append("')");
		}
		return commCondSB.toString();
	}

	//筛选条件  类别指标筛选
	public String selectCondition(AdRecoveryDto req, String bieming) {
		final Integer os = req.getOs();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
		final String appchl = req.getAppchlArr();
		final String parentchlArr = req.getParentchlArr();

		// 通用筛选条件
		StringBuilder commCondSB = new StringBuilder();
		if (os != null) {
			commCondSB.append(" AND os = ").append(os);
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append(" AND game_main in (" + pgidArr + ")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append(" AND game_sub in (" + gameidArr + ")");
		}
		if (StringUtils.isNotBlank(appchl)) {
			commCondSB.append(" AND chl_base = '").append(appchl).append("'");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append(" AND chl_main IN ('").append(parentchlArr.replaceAll(",", "','")).append("')");
		}

		return commCondSB.toString();
	}

	public String selectActiveCondition(AdRecoveryDto req, String bieming) {
		final Integer os = req.getOs();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
		final String parentchl = req.getParentchlArr();
		final String appchl = req.getAppchlArr();
		final String parentchlArr = req.getParentchlArr();

		// 通用筛选条件
		StringBuilder commCondSB = new StringBuilder();
		if (os != null) {
			commCondSB.append(" AND os = ").append(os);
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append(" AND d_game_main in (" + pgidArr + ")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append(" AND d_game_sub in (" + gameidArr + ")");
		}
		if (StringUtils.isNotBlank(parentchl)) {
			commCondSB.append(" AND d_chl_main = '").append(parentchl).append("'");
		}
		if (StringUtils.isNotBlank(appchl)) {
			commCondSB.append(" AND d_chl_base = '").append(appchl).append("'");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append(" AND d_chl_main IN ('").append(parentchlArr.replaceAll(",", "','")).append("')");
		}

		return commCondSB.toString();
	}

	//筛选条件  类别指标筛选
	public String selectCommonCondition(AdRecoveryDto req, String bieming) {
		String period = req.getPeriod();
		Integer isSys = req.getIsSys();
		final String userIds = req.getUserIds();
		final String adAccounts = req.getAdAccounts();
		final Integer os = req.getOs();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
		final String parentchlArr = req.getParentchlArr();

		String appchlArr = req.getAppchlArr();

		final String adidArr = req.getAdidArr();
		final String advertiserIdArr = req.getAdvertiserIdArr();

		// 通用筛选条件
		StringBuilder commCondSB = new StringBuilder();
		if (os != null) {
			commCondSB.append(" AND os = ").append(os);
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append(" AND pgid in (").append(pgidArr).append(')');
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append(" AND gameid in (").append(gameidArr).append(')');
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append(" AND parentchl IN ('").append(parentchlArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(appchlArr)) {
			if (appchlArr.contains(",")) {
				commCondSB.append(" AND appchl in ('").append(appchlArr.replaceAll(",", "','")).append("')");
			} else {
				commCondSB.append(" AND appchl  = '").append(appchlArr).append("'");
			}
		}

		return commCondSB.toString();
	}

	public String selectComConditionWd(AdRecoveryDto req, String bieming) {
		final String deptIdArr = req.getDeptIdArr();
		final String userGroupIdArr = req.getUserGroupIdArr();
		final String investorArr = req.getInvestorArr();

		StringBuilder commCondSB = new StringBuilder();
		if (StringUtils.isNotBlank(deptIdArr)) {
			commCondSB.append(" AND deptId IN (").append(deptIdArr).append(")");
		}
		if (StringUtils.isNotBlank(userGroupIdArr)) {
			commCondSB.append(" AND userGroupId IN (").append(userGroupIdArr).append(")");
		}
		if (StringUtils.isNotBlank(investorArr)) {
			commCondSB.append(" AND investor IN (").append(investorArr).append(")");
		}
		return commCondSB.toString();
	}

	public String selectConditionWd(AdRecoveryDto req, String bieming) {
		final String deptIdArr = req.getDeptIdArr();
		final String userGroupIdArr = req.getUserGroupIdArr();
		final String investorArr = req.getInvestorArr();

		StringBuilder commCondSB = new StringBuilder();
		if (StringUtils.isNotBlank(deptIdArr)) {
			commCondSB.append(" AND dept_id IN (").append(deptIdArr).append(')');
		}
		if (StringUtils.isNotBlank(userGroupIdArr)) {
			commCondSB.append(" AND dept_group_id IN (").append(userGroupIdArr).append(')');
		}
		if (StringUtils.isNotBlank(investorArr)) {
			commCondSB.append(" AND manage IN (").append(investorArr).append(')');
		}
		return commCondSB.toString();
	}

	// 入参时间转Str
	private String convertQueryTime(Long queryTime) {
		if (Objects.nonNull(queryTime)) {
			String time = String.valueOf(queryTime.intValue());
			if (StringUtils.isNotBlank(time)) {
				return String.format("%s-%s-%s", time.substring(0, 4), time.substring(4, 6), time.substring(6, 8));
			}
		}
		return null;
	}

}
